Release 10.1A: OpenEdge Development:
ProDataSets

Elements of a Data Access object

Given these basic principles of separation of data source specifics from the rest of the application, what then are the elements that properly belong in a Data Access object? Keep in mind as we ask this question that a Data Access object is not a Progress 4GL language construct or anything else with a specific meaning or structure. It is a concept that can be useful to you to think about as you architect your application. We capitalize the term only to identify it as an implementation of a part of the Reference Architecture.

The sample procedures from earlier chapters are a reasonable starting point to identify the proper elements to include in a Data Access object. In later documents we’ll extend these basic elements with a more detailed API to provide a template with additional standard behavior. In general you can think of a Data Access object as being paired with a Business Entity object that manages the actual ProDataSet instance data for the rest of the application and applies validation and other business logic. This mapping might not be one-to-one, however. For various reasons you might have multiple Business Entities that use the same data sources and therefore the same Data Access object.

There is also no reason why a Data Access object or any other “object” in your application must be thought of as a single Progress 4GL procedure. A base procedure can extend its behavior through the use of super procedures or other forms of procedure library. Or a single procedure could manage multiple Data Access objects, if that’s appropriate to your situation. The important thing is how you think about organizing your definitions and the code to manage them, and how this fits in with the rest of your application.

So given these points, let’s look at some of the elements of a typical Data Access object.

ProDataSet and temp-table definitions

As we’ve discussed in earlier chapters, in most cases your ProDataSet and temp-table definitions on the server-side of the application (at least) will be statically defined, because they represent specific sets of data with their own distinct structure and characteristics. Therefore the first element in a typical Data Access object will be the temp-table and ProDataSet definitions for the data it retrieves from the data sources.

There is no reason why some Data Access objects couldn’t be based on dynamic ProDataSets, especially if they represent a collection of tables or sets of table in the database that all have a similar structure and are all processed in the same way. As always, if you support these kinds of variant objects, you should make sure you structure them in such a way that other objects that communicate with them don’t need to know whether their data comes from static or dynamic objects. Since Progress supports freely interchanging static and dynamic temp-tables and ProDataSets when you pass them as parameters, this should not be difficult.

Here are the include file references from the earlier example procedure OrderSource.p, which is really an example of a Data Access object:

{dsOrderTT.i} /* …where the include file has these definitions: DEFINE TEMP-TABLE ttOrder LIKE Order FIELD OrderTotal AS DECIMAL FIELD CustName LIKE Customer.NAME FIELD RepName LIKE SalesRep.RepName. DEFINE TEMP-TABLE ttOline LIKE OrderLine BEFORE-TABLE ttOlineBefore. DEFINE TEMP-TABLE ttItem LIKE ITEM INDEX ItemNum IS UNIQUE ItemNum. */ {dsOrder.i} /* … where the include file has this definition: DEFINE DATASET dsOrder FOR ttOrder, ttOline, ttItem DATA-RELATION OrderLine FOR ttOrder, ttOline RELATION-FIELDS (OrderNum, OrderNum) DATA-RELATION LineItem FOR ttOline, ttItem RELATION-FIELDS (ItemNum, ItemNum) REPOSITION. */

These are defined as two separate include files just to make it possible to include them independently in a procedure where you don’t need or want both.

How are these definitions used in the Data Access object? If you remember the interaction from the sample procedure OrderSource.p and its Business Entity, OrderEntity.p, the definitions are used for compilation only, so that Progress can understand references to temp-tables and their fields in the internal procedures inside the Data Access procedure.

Why is this? The Business Entity object, described later, “owns” the data for its instance of the ProDataSet, whether that’s all the data for an Order or summary information for all of a SalesRep’s Orders or whatever else it may be. Every running instance of the Business Entity represents a distinct instance of its ProDataSet and a distinct set of data rows.

By contrast, the Data Access object only serves to populate the Business Entity’s ProDataSet with data, and where necessary to assist in getting updates back to the data source. In any call, the actual ProDataSet instance will be passed in from the Business Entity or other requesting procedure BY-REFERENCE, so that it replaces the locally defined instance that is used to compile the Data Access procedure. Since each call to the Data Access object passes in a ProDataSet instance, there should be no reason why a single running instance of the Data Access procedure should not be able to serve all requests. It needs to be designed to make sure that there is no context kept from call to call that would prevent this, or else context needs to be managed in some way if this is necessary.

The local temp-table and ProDataSet definitions also cause an instance of the ProDataSet and its temp-tables to be instantiated in the data access procedure. Since this instance is not actually used at runtime, it is important to observe the guideline discussed in earlier chapters concerning ProDataSets passed BY-REFERENCE, namely that you should not use the handle or any other references to this ProDataSet instance from the procedure’s main block in code located within one of the procedure’s internal procedures that receives the ProDataSet as a parameter.

Data-Source queries

The next thing to define in a Data Access object is any database queries the procedure uses to fill the ProDataSet tables. In the sample procedure OrderSource.p, there is one query for the top-level table, which is needed because it involves a join of multiple database tables. For example:

DEFINE QUERY qOrder FOR Order, Customer, SalesRep.

Because these queries define the nature of the Data-Source, and because they are used in the FILL process and the FILL events that the Data Access object defines, they belong here rather than in the Business Entity.

In cases where there is no standard database Data-Source, there may be no queries of this kind. In that case, whatever other definitions may be needed to allow the FILL event procedures to do their job belong here.

Data-Source definitions

If the ProDataSet can be filled from Progress database or Progress DataServer-managed tables, then you can define Data-Source objects to handle the fill. These name the database table that is the source of data, or the query that identifies one or more tables, along with the key fields for the tables. The dsOrder ProDataSet used in the sample procedures has these Data-Source definitions:

DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder Order KEYS (OrderNum), Customer KEYS (CustNum), SalesRep KEYS (SalesRep). DEFINE DATA-SOURCE srcOline FOR OrderLine. DEFINE DATA-SOURCE srcItem FOR ITEM KEYS (ItemNum).

The Data-Sources in effect define the part of the FILL process that Progress can handle automatically for you. In the simplest case this is everything, and no special FILL logic is needed. In other cases the standard FILL behavior must be supplemented by additional code. This goes into FILL event procedures that are also part of the Data Access object. In cases where there are no standard Data-Source objects, then all the FILL logic goes into the event procedures.

FILL event procedures

If you need special logic to supplement or to fully control the FILL process, the procedures that implement that logic also go into the Data Access object. They can be attached to any ProDataSet instance passed in to the procedure using the SET-CALLBACK-PROCEDURE method. They belong there because they have full knowledge of the specifics of the sources of data, whether they are standard Progress Data-Sources or not, and how the data is mapped to the internal representation used by the rest of the application.

For example, OrderSource.p has these FILL event procedures:

PROCEDURE postOlineFill: DEFINE INPUT PARAMETER DATASET FOR dsOrder. DEFINE VARIABLE dTotal AS DECIMAL NO-UNDO. /* Here as well "ttOline" uses the local definition for compilation but points to the ttOline table in the input parameter at run time. */ FOR EACH ttOline WHERE ttOline.OrderNum = ttOrder.OrderNum: dTotal = dTotal + ttOline.ExtendedPrice. END. ttOrder.OrderTotal = dTotal. END PROCEDURE. /* postOlineFill */ PROCEDURE postItemRowFill: DEFINE INPUT PARAMETER DATASET FOR dsOrder. DEFINE VARIABLE iType AS INTEGER NO-UNDO. DEFINE VARIABLE cItemTypes AS CHARACTER NO-UNDO INIT "BASEBALL,CROQUET,FISHING,FOOTBALL,GOLF,SKI,SWIM,TENNIS". DEFINE VARIABLE iTypeNum AS INTEGER NO-UNDO. DEFINE VARIABLE cType AS CHARACTER NO-UNDO. DO iType = 1 TO NUM-ENTRIES(cItemTypes): cType = ENTRY(iType, cItemTypes). IF INDEX(ttItem.ItemName, cType) NE 0 THEN ttItem.ItemName = REPLACE(ttItem.ItemName, cType, cType). END. END PROCEDURE. /* postItemRowFill */

Procedure postOlineFill calculates the Order total, and procedure postItemRowFill reformats the Item Name for each Item.

Functions to attach and detach the Data-Sources from a ProDataSet

The reason why all the FILL event procedures can be defined in the Data Access object, even though the procedure’s ProDataSet instance isn’t really used to hold data at runtime, is that the Data Access object takes responsibility for attaching those procedures to any ProDataSet instance passed into the Data Access object. OrderSource.p has an attachDataSet function to do this:

FUNCTION attachDataSet RETURNS LOGICAL (INPUT phDataSet AS HANDLE): phDataSet:GET-BUFFER-HANDLE("ttOline"):SET-CALLBACK-PROCEDURE ("AFTER-FILL", "postOlineFill", THIS-PROCEDURE). phDataSet:GET-BUFFER-HANDLE("ttItem"):SET-CALLBACK-PROCEDURE ("AFTER-ROW-FILL", "postItemRowFill", THIS-PROCEDURE). phDataSet:GET-BUFFER-HANDLE("ttOrder"):ATTACH-DATA-SOURCE (DATA-SOURCE srcOrder:HANDLE, "Customer.Name,CustName"). phDataSet:GET-BUFFER-HANDLE("ttOline"):ATTACH-DATA-SOURCE (DATA-SOURCE srcOline:HANDLE). phDataSet:GET-BUFFER-HANDLE("ttItem"):ATTACH-DATA-SOURCE (DATA-SOURCE srcItem:HANDLE). END FUNCTION. /* attachDataSet */

This could just as easily be an internal procedure, of course. A more thorough implementation of the function should check the return value for each SET-CALLBACK-PROCEDURE and ATTACH-DATA-SOURCE method and return an error status to the caller. The SET-CALLBACK-PROCEDURE methods attach the needed FILL events handlers, and the ATTACH-DATA-SOURCE methods connect the ProDataSet instance to its database tables.

If a repository or other persistent store holds the field mapping and Data-Source mapping for the ATTACH-DATA-SOURCE methods, and the callback procedure names and locations for the SET-CALLBACK-PROCEDURE methods, then this function could become generic, and be part of a standard procedure that supports all Data Access objects (as a super procedure, for instance).

There should also be a function or procedure to detach all Data-Sources, as in this example:

FUNCTION detachDataSet RETURNS logic (INPUT phDataSet AS HANDLE): DEFINE VARIABLE iBuff AS INTEGER NO-UNDO. DO iBuff = 1 TO DATASET dsOrder:NUM-BUFFERS: phDataSet:GET-BUFFER-HANDLE(iBuff):DETACH-DATA-SOURCE(). END. END FUNCTION. /* detachDataSet */

As this sample shows, this function can easily be made generic, so it is written only once and resides in a Data Access support procedure.

It’s important to note that once the Data-Sources are attached and any callback procedures established, the calling procedure that passed in the ProDataSet instance to attachDataSet can simply do a FILL if the Data-Source definitions and FILL event handlers fully determine what rows to populate the ProDataSet with. In other words, once the ProDataSet is returned to the caller, the Data-Sources and callback procedures remain associated with it through its handle, so these associations remain intact even in the ProDataSet instance is passed around within the session, so that other procedures and invoke a FILL or other methods on that handle from anywhere in the session. In many cases, however, a FILL will require that the ProDataSet’s queries first be prepared to retrieve only a selected set of related rows before doing the FILL. An API for such calls is discussed next.

Data retrieval API

There can be both standard and specialized API calls that populate a ProDataSet. Depending on the data in the ProDataSet, there will be different sets of data that are useful to the application. In the case of the sample Order Entity, there’s a call to retrieve all data for a single Order Number and another call to retrieve summary data in just the Order table for some related set of Orders. What these calls have in common is that they require code that is “data source aware” to prepare the right queries or otherwise adjust the parameters of the FILL. For this reason, they belong in the Data Access object.

In general, it is a good idea not to allow anything except the Data Access object’s Business Entity to use its API directly, so that if the user interface or some other part of the application needs to request data, it should use the API of the Business Entity, which can then turn around and use the API of the Data Access object to retrieve the right data. In the sample procedures, there’s a fetchOrder procedure that accepts an Order Number and returns a ProDataSet as output. This runs this procedure of the same name in the Data Access object OrderSource.p, passing in the Order Number, along with the Business Entity’s ProDataSet instance as an INPUT-OUTPUT parameter:

PROCEDURE fetchOrder: DEFINE INPUT PARAMETER piOrderNum AS INTEGER NO-UNDO. DEFINE INPUT-OUTPUT PARAMETER DATASET FOR dsOrder. QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum = " + STRING(piOrderNum) + ", FIRST Customer OF Order, FIRST SalesRep OF Order"). /* Note that this reference to dsOrder is not using the local definition but rather the actual dataset instance being passed in. */ IF VALID-HANDLE(DATASET dsOrder:GET-BUFFER-HANDLE(1):DATA-SOURCE) THEN DATASET dsOrder:FILL(). ELSE DO: DATASET dsOrder:GET-BUFFER-HANDLE(1):TABLE-HANDLE:ERROR-STRING = "Data-Sources not attached". DATASET dsOrder:ERROR = TRUE. END. RETURN. END PROCEDURE. /* fetchOrder */

The primary reason for this separation is to avoid having data-source-aware code, such as the QUERY-PREPARE method, in the Business Entity. If the nature of the data source ever changes, then all the references to it are captured in the Data Access object and can be changed together.

As you can see, fetchOrder requires that the caller previously run the attachDataSet method to attach Data-Sources and callback procedures. It could also be good practice to embed a check inside each API call in the Data Access object that assumes that the Data-Sources and any callback procedures are attached. The check can then run the attach function as needed, rather than depending on the caller to do this first.

For example, you could conditionally invoke attachDataSet from within fetchOrder. First you could define a function prototype for attachDataSet at the top of the procedure, as shown:

FUNCTION attachDataSet RETURNS LOGICAL (INPUT phDataSet AS HANDLE) FORWARD.

Then fetchOrder can invoke it if the Data-Sources are not attached rather than raising an error:

PROCEDURE fetchOrder: DEFINE INPUT PARAMETER piOrderNum AS INTEGER NO-UNDO. DEFINE INPUT-OUTPUT PARAMETER DATASET FOR dsOrder. DEFINE VARIABLE hDataSet AS HANDLE NO-UNDO. /* Note that this reference to dsOrder is not using the local definition but rather the actual dataset instance being passed in. */ hDataSet = DATASET dsOrder:HANDLE. IF NOT VALID-HANDLE(DATASET dsOrder:GET-BUFFER-HANDLE(1):DATA-SOURCE) THEN attachDataSet(INPUT hDataSet). QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum = " + STRING(piOrderNum) + ", FIRST Customer OF Order, FIRST SalesRep OF Order"). DATASET dsOrder:FILL(). /* The attach call makes the error message unnecessary. ELSE DO: DATASET dsOrder:GET-BUFFER-HANDLE(1):TABLE-HANDLE:ERROR-STRING = "Data-Sources not attached". DATASET dsOrder:ERROR = TRUE. END. */ RETURN. END PROCEDURE. /* fetchOrder */

Specialized update API

The ProDataSet update examples use a generic update procedure called commitChanges.p to return updates to the database. It illustrates how you can attach validation logic procedures in a standard way much as you would do for SmartDataObjects in the ADM2. In a large percentage of cases this can provide you with a general purpose update mechanism that applies to many different Business Entities.

There are however cases where a generic procedure won’t be adequate. In particular, if there are no standard Data-Sources to attach to the ProDataSet, because the data source is unstructured data, then there can also be no built-in update support, and you will need to write your own update API to accept changed ProDataSets and do whatever is necessary to get those changes back to the data sources. As with the retrieval API, it is best if any requests from outside the Business Entity go through the Business Entity, which can then make the right request of the Data Access object to get the job done.